<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Users_mod extends CI_Model {

    private $_dbTableUsers = 'users';
    private $_dbTableLogOnline = 'log_online';
    private $_dbTableUserGroups = 'user_groups';
    private $_dbTableAffiliate = 'affiliate';
    private $_dbTableMessages = 'messages';
    private $_dbTAbleBilling = 'billing';
    private $_dbTableStates = 'states';
    private $_dbTableImages = 'images';

    /**
     *  class sonstructor
     */
    function __construct() {
        // Call the Model constructor
        parent::__construct();
    }

    public function getOnlineUsers() {
        $query = $this->db->select(
                        $this->_dbTableUsers . '.id,' .
                        $this->_dbTableUsers . '.email,' .
                        $this->_dbTableUsers . '.gender,' .
                        $this->_dbTableUsers . '.username,' .
                        $this->_dbTableUsers . '.user_group,' .
                        $this->_dbTableUsers . '.coins,' .
                        $this->_dbTableUsers . '.bonus_coins,' .
                        $this->_dbTableUsers . '.expenditure,' .
                        $this->_dbTableUsers . '.registration_date,' .
                        $this->_dbTableUsers . '.timestamp'
                        //"COUNT(`".$this->_dbTableAffiliate."`.`id`) as num_affiliate "
                        //$this->_dbTableAffiliate.".id"
                )
                ->where($this->_dbTableUsers.'.user_group',3) // Samo obicni useri
                ->from($this->_dbTableUsers)
                ->join($this->_dbTableLogOnline, $this->_dbTableUsers . ".id = " . $this->_dbTableLogOnline . ".user_id")
//                        ->join($this->_dbTableAffiliate,$this->_dbTableUsers.".id = ".$this->_dbTableAffiliate.".user_id","LEFT")
                ->get();
        //echo $this->db->last_query();
        $errors = $this->db->_error_message();
        if (!empty($errors)) {
            throw new Exception("SQL Error: " . $errors);
        } else {
            
        }

        if ($query->num_rows() > 0) {
            $users = $query->result();
            $query->free_result();
        } else {
            $users = NULL;
        }

        $result = array();
        $row = new stdClass();
        foreach ($users as $user) {
            $row = $user;

            // num_affiliate
            $query = $this->db->select(" COUNT(`" . $this->_dbTableAffiliate . "`.`id`) as num_affiliate ")
                    ->from($this->_dbTableAffiliate)
                    ->where('user_id = ' . $user->id)
                    ->where('released', 1)
                    ->get();
            //echo $this->db->last_query();
            $errors = $this->db->_error_message();
            if (!empty($errors)) {
                throw new Exception("SQL Error: " . $errors);
            } else {
                
            }

            if ($query->num_rows() > 0) {
                $affiliate = $query->row();
                $row->num_affiliate = $affiliate->num_affiliate;
                $query->free_result();
            } else {
                //$affiliate = NULL;
                $row->num_affiliate = 0;
            }

            // Last message
            $query = $this->db->select(" time ")
                    ->from($this->_dbTableMessages)
                    ->where('from', $user->id)
                    ->order_by('time', 'desc')
                    ->get();
            //echo $this->db->last_query();
            $errors = $this->db->_error_message();
            if (!empty($errors)) {
                throw new Exception("SQL Error: " . $errors);
            } else {
                
            }

            if ($query->num_rows() > 0) {
                $message = $query->row();
                $row->last_message_time = $message->time;
                $query->free_result();
            } else {
                //$affiliate = NULL;
                $row->last_message_time = " - ";
            }

            $result[] = $row;
        }

        return $result;
    }

    public function getAllUsers($filter = NULL) {
        $this->db->select(
                $this->_dbTableUsers . '.id,' .
                $this->_dbTableUsers . '.email,' .
                $this->_dbTableUsers . '.gender,' .
                $this->_dbTableUsers . '.look_for,' .
                $this->_dbTableUsers . '.birthdate,' .
                $this->_dbTableUsers . '.state,' .
                $this->_dbTableUsers . '.zip_code,' .
                $this->_dbTableUsers . '.username,' .
                $this->_dbTableUsers . '.user_group,' .
                $this->_dbTableUsers . '.ip_address,' .
                $this->_dbTableUsers . '.coins,' .
                $this->_dbTableUsers . '.bonus_coins,' .
                $this->_dbTableUsers . '.expenditure,' .
                $this->_dbTableUsers . '.registration_date,' .
                $this->_dbTableUsers . '.status,' .
                $this->_dbTableUsers . '.timestamp'
        );
        if (isset($filter['username']) && $filter['username'] != "")
            $this->db->like('username', $filter['username']);
        if (isset($filter['id']) && $filter['id'] != "")
            $this->db->like('id', $filter['id']);
        if (isset($filter['email']) && $filter['email'] != "")
            $this->db->like('email', $filter['email']);
        if (isset($filter['start_reg_date']) && $filter['start_reg_date'] != "")
            $this->db->where('registration_date >=', $filter['start_reg_date']);
        if (isset($filter['end_reg_date']) && $filter['end_reg_date'] != "")
            $this->db->where('registration_date <=', $filter['end_reg_date']);
        if (isset($filter['gender']) && $filter['gender'] != "")
            $this->db->where('gender', $filter['gender']);
        if (isset($filter['status']) && $filter['status'] != "")
            $this->db->where('status', $filter['status']);
//      if( isset($filter['team']) && $filter['team'] != "" )
//         $this->db->where('team',$filter['team']);
        $this->db->order_by($this->_dbTableUsers . '.id', 'DESC');
        $query = $this->db->get($this->_dbTableUsers);

        //echo $this->db->last_query();
        $errors = $this->db->_error_message();
        if (!empty($errors)) {
            throw new Exception("SQL Error: " . $errors);
        } else {
            
        }

        if ($query->num_rows() > 0) {
            $users = $query->result();
            $query->free_result();
        } else {
            $users = NULL;
        }

        $result = array();
        $row = new stdClass();
        if (count($users) > 0) {
            foreach ($users as $user) {
                $row = $user;
                $row->age = date("Y") - ceil(substr($row->birthdate, 0, 4));
                // num_affiliate
                $query = $this->db->select(" COUNT(`" . $this->_dbTableAffiliate . "`.`id`) as num_affiliate ")
                        ->from($this->_dbTableAffiliate)
                        ->where('user_id = ' . $user->id)
                        ->where('released', 1)
                        ->get();
                //echo $this->db->last_query();
                $errors = $this->db->_error_message();
                if (!empty($errors)) {
                    throw new Exception("SQL Error: " . $errors);
                } else {
                    
                }

                if ($query->num_rows() > 0) {
                    $affiliate = $query->row();
                    $row->num_affiliate = $affiliate->num_affiliate;
                    $query->free_result();
                } else {
                    //$affiliate = NULL;
                    $row->num_affiliate = 0;
                }

                $result[] = $row;
            }
        }

        return $result;
    }

    public function getUserData($id, $like_array = true) {
        $this->db->select(
                $this->_dbTableUsers . '.id,' .
                $this->_dbTableUsers . '.email,' .
                $this->_dbTableUsers . '.gender,' .
                $this->_dbTableUsers . '.look_for,' .
                $this->_dbTableUsers . '.birthdate,' .
                $this->_dbTableUsers . '.state,' .
                $this->_dbTableUsers . '.zip_code,' .
                $this->_dbTableUsers . '.username,' .
                $this->_dbTableUsers . '.user_group,' .
                $this->_dbTableUsers . '.ip_address,' .
                $this->_dbTableUsers . '.coins,' .
                $this->_dbTableUsers . '.bonus_coins,' .
                $this->_dbTableUsers . '.expenditure,' .
                $this->_dbTableUsers . '.registration_date,' .
                $this->_dbTableUsers . '.status,' .
                $this->_dbTableUsers . '.timestamp'
        );

        $this->db->where('id', $id);

        $query = $this->db->get($this->_dbTableUsers);

        //echo $this->db->last_query();
        $errors = $this->db->_error_message();
        if (!empty($errors)) {
            throw new Exception("SQL Error: " . $errors);
        } else {
            
        }

        if ($query->num_rows() == 1) {
            if ($like_array)
                $user = $query->row_array(); // Return ONE ROW ARRAY
            else
                $user = $query->row(); // RETRURN ONE ROW 

            $query->free_result();
        } else {
            $users = NULL;
        }

        if ($like_array)
            $user['age'] = date("Y") - ceil(substr($user['birthdate'], 0, 4));
        else
            $user->age = date("Y") - ceil(substr($user->birthdate, 0, 4));
        // num_affiliate
        $query = $this->db->select(" COUNT(`" . $this->_dbTableAffiliate . "`.`id`) as num_affiliate ")
                ->from($this->_dbTableAffiliate)
                ->where('user_id = ' . $id)
                ->where('released', 1)
                ->get();
        //echo $this->db->last_query();
        $errors = $this->db->_error_message();
        if (!empty($errors)) {
            throw new Exception("SQL Error: " . $errors);
        } else {
            
        }

        if ($query->num_rows() > 0) {
            $affiliate = $query->row();
            $num_affiliate = $affiliate->num_affiliate;
            $query->free_result();
        } else {
            //$affiliate = NULL;
            $num_affiliate = 0;
        }

        if ($like_array)
            $user['affiliate'] = $num_affiliate;
        else
            $user->affiliate = $num_affiliate;

        return $user;
    }

    public function getUserEmail($id) {
        $query = $this->db->select('email')->from($this->_dbTableUsers)->where('id', $id)->get();
        $errors = $this->db->_error_message();
        if (!empty($errors)) {
            throw new Exception("SQL Error: " . $errors);
        } else {
            
        }

        if ($query->num_rows() == 1) {
            $user = $query->row(); // RETRURN ONE ROW 
            $query->free_result();
        } else {
            $user = NULL;
        }

        return $user->email;
    }

    public function getUserActivity($id) {
        $this->db->select(
                $this->_dbTableUsers . '.id,' .
                $this->_dbTableUsers . '.username'
        );

        $this->db->where('id', $id);

        $query = $this->db->get($this->_dbTableUsers);

        //echo $this->db->last_query();
        $errors = $this->db->_error_message();
        if (!empty($errors)) {
            throw new Exception("SQL Error: " . $errors);
        } else {
            
        }

        if ($query->num_rows() == 1) {
            $user = $query->row(); // RETRURN ONE ROW
            $query->free_result();
        } else {
            $users = NULL;
        }

        $result['user'] = $user;

        //avatar
        $query = $this->db->select('name')
                ->from($this->_dbTableImages)
                ->where('user_id = ' . $user->id)
                ->order_by('main', 'desc')
                ->get();
        $errors = $this->db->_error_message();
        if (!empty($errors)) {
            throw new Exception("SQL Error: " . $errors);
        } else {
            
        }

        if ($query->num_rows() > 0)  {
            $image = $query->row(); // RETRURN ONE ROW
            $query->free_result();
        } else {
            $image = NULL;
        }
        $result['image'] = $image;
        // Message's
        $query = $this->db->distinct()
                ->select(" COUNT({$this->_dbTableMessages}.`id`) as nr_messages, from, to, MAX(time) as time, "
                        //. $this->_dbTableUsers.".username as to " 
                        . " (SELECT username FROM {$this->_dbTableUsers} WHERE {$this->_dbTableUsers}.`id` = `from` ) as username_from, "
                        . " (SELECT id FROM {$this->_dbTableUsers} WHERE {$this->_dbTableUsers}.`id` = `from` ) as id_from, "
                        . " (SELECT username FROM {$this->_dbTableUsers} WHERE {$this->_dbTableUsers}.`id` = `to` ) as username_to, "
                        . " (SELECT id FROM {$this->_dbTableUsers} WHERE {$this->_dbTableUsers}.`id` = `to` ) as id_to, "
                )
                ->from($this->_dbTableMessages)
                //->join( $this->_dbTableUsers, " `to` = ".$this->_dbTableUsers.".id" )  
                ->where('from = ' . $user->id)
                ->or_where('to = ' . $user->id)
                ->group_by(array('from', 'to'))
                ->order_by('time', 'desc')
                ->get();
        // echo $this->db->last_query();
        $errors = $this->db->_error_message();
        if (!empty($errors)) {
            throw new Exception("SQL Error: " . $errors);
        } else {
            
        }

        if ($query->num_rows() > 0) {
            $messages = $query->result();
            $query->free_result();
        } else {
            $messages = NULL;
        }
        $result['messages'] = $messages;


        // transactions
        $query = $this->db->select("*")
                ->from($this->_dbTAbleBilling)
                ->where('user_id = ' . $id)
                ->get();
        // echo $this->db->last_query();
        $errors = $this->db->_error_message();
        if (!empty($errors)) {
            throw new Exception("SQL Error: " . $errors);
        } else {
            
        }

        if ($query->num_rows() > 0) {
            $transactions = $query->result();
            $query->free_result();
        } else {
            $transactions = NULL;
        }
        $result['transactions'] = $transactions;


        return $result;
    }

    public function saveUser($data) {
        unset($data['affiliate']);

        if (isset($data['id']) && $data['id'] != "") { // Update
            $this->db->where('id', $data['id'])->update($this->_dbTableUsers, $data);
        } else { // Insert
            $data['password'] = md5($data['password']);
            $data['registration_date'] = date("Y-m-d H:i:s", time());
            $data['ip_address'] = "0.0.0.0";
            $this->db->insert($this->_dbTableUsers, $data);
        }

        $errors = $this->db->_error_message();
        if (!empty($errors)) {
            throw new Exception("SQL Error: " . $errors);
        } else {
            
        }
    }

    public function getStates() {
        $query = $this->db->select('*')->get($this->_dbTableStates);

        // echo $this->db->last_query();
        $errors = $this->db->_error_message();
        if (!empty($errors)) {
            throw new Exception("SQL Error: " . $errors);
        } else {
            
        }

        if ($query->num_rows() > 0) {
            $result = $query->result();
            $query->free_result();
        } else {
            $result = NULL;
        }

        return $result;
    }

    public function getUserGroups() {
        $query = $this->db->select('*')->get($this->_dbTableUserGroups);

        // echo $this->db->last_query();
        $errors = $this->db->_error_message();
        if (!empty($errors)) {
            throw new Exception("SQL Error: " . $errors);
        } else {
            
        }

        if ($query->num_rows() > 0) {
            $result = $query->result();
            $query->free_result();
        } else {
            $result = NULL;
        }

        return $result;
    }

}

// end class Users_mod

/*
  $query = $this->db->select('*')
  ->from()
  ->where()
  ->get();
  //echo $this->db->last_query();
  $errors = $this->db->_error_message();
  if (!empty($errors)) {
  throw new Exception("SQL Error: " . $errors);
  } else { }

  if ($query->num_rows() > 0) {
  $result = $query->result();
  $query->free_result();
  } else {
  $result = NULL;
  }

  return $result;
 */

// end file: administration/models/users_mod.php
?>
